package com.geologyoa.persist;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.geologyoa.domain.Dwqk01;
import com.geologyoa.commons.JdbcUtil;
import com.geologyoa.commons.commUtils;


public class Dwqk01DaoImpl implements Dwqk01Dao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	public Boolean AddDwqk(Dwqk01 dwqk01) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   Calendar cal = Calendar.getInstance();
			 int year = cal.get(Calendar.YEAR);
			 sql = "select  dwqk0102 from DWQK01 where dwqk0102 like 'DW"+year+"____' order by dwqk0102 desc ";
			 String dwqk0102 = "";
			
		  	try {
		  		ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				if(rs.next()){
					String maxCode = rs.getString("dwqk0102");
					int tmpCode =Integer.parseInt(maxCode.substring(6))+1;
					dwqk0102 = maxCode.substring(0, 6)+commUtils.completStr(String.valueOf(tmpCode), 4);
				}else{
					dwqk0102 = "DW"+year+"0001";
				}
		  		sql = "insert into dwqk01 values(oa_seq.nextval,?,?,?,?," +
		  				"?,?,?,?,?," +
		  				"?,?,?,?,?," +
		  				"?)";
				
				ps = conn.prepareStatement(sql);
				ps.setString(1, dwqk0102);
				ps.setString(2,dwqk01.getDwqk0103());
				ps.setString(3, dwqk01.getDwqk0104());
				ps.setString(4, dwqk01.getDwqk0105());
				ps.setString(5, dwqk01.getDwqk0106());
				ps.setString(6, dwqk01.getDwqk0107());
				ps.setString(7, dwqk01.getDwqk0108());
				ps.setDouble(8, dwqk01.getDwqk0109());
				ps.setString(9, dwqk01.getDwqk0110());
				ps.setString(10, dwqk01.getDwqk0111());
				ps.setString(11, dwqk01.getDwqk0113());
				ps.setString(12, dwqk01.getDwqk0114());
				ps.setString(13, dwqk01.getDwqk0116());
				ps.setString(14, dwqk01.getDwqk0117());
				ps.setString(15, dwqk01.getDwqk0118());
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	public Boolean CheckDwqkByName(String DWQK0103) {
		Boolean flag =false;
		Calendar cal = Calendar.getInstance();
		int year = cal.get(Calendar.YEAR);
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from dwqk01 where DWQK0103=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, DWQK0103);
			// modify by cuiyi 20130330   精简代码
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
			/*int number=ps.executeUpdate();
			if(number>0)
			{
				flag=true;
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		} finally
		{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}
	public Boolean CheckDwqkByIdAndName(int id, String DWQK0103){
		Boolean flag =false;
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from dwqk01 where DWQK0101=? and DWQK0103=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, DWQK0103);
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally
		{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}
	public Boolean CheckDwqkByIdAndZZType(int id, String zzType){
		Boolean flag =false;
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from DWZZ01 where DWZZ0102=? and DWZZ0104=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, zzType);
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally
		{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}
	
	public Boolean CheckDwqkByIdAndZZTypeExceptZZid(int zzid, int id,
			String zzType) {
		Boolean flag =false;
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from DWZZ01 where DWZZ0102=? and DWZZ0104=? and DWZZ0101!=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, zzType);
			ps.setInt(3, zzid);
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally
		{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}
	public List<Dwqk01> SerachDwqkByZZType(String DWQK0103, String DWQK0104,
			String DWQK0107, String DWQK0110, String ZZType, String ZZLevel) {
		Dwqk01 dwqk01=null;
		List<Dwqk01> dwqk01List=new ArrayList<Dwqk01>();
		   conn=JdbcUtil.getConnection();
		   StringBuffer sql = new StringBuffer();	
		   		sql.append("select * from dwqk01,dwzz01 where dwqk0101=dwzz0102 ");
		      if(DWQK0103!=null&&!DWQK0103.equals("")&&!DWQK0103.equals("null"))
		      {
		    	  sql.append(" and DWQK0103 like '%"+DWQK0103+"%'");  
		      }
		    	  
		      if(DWQK0104!=null&&!DWQK0104.equals("")&&!DWQK0104.equals("null"))
		      {
		    	  sql.append(" and DWQK0104='"+DWQK0104+"'");  
		      }
		      if(DWQK0107!=null&&!DWQK0107.equals("")&&!DWQK0107.equals("null"))
		      {
		    	  sql.append(" and DWQK0107='"+DWQK0107+"'");  
		      }
		      if(DWQK0110!=null&&!DWQK0110.equals("")&&!DWQK0110.equals("null"))
		      {
		    	  sql.append(" and DWQK0110 like '%"+DWQK0110+"%'");  
		      }
		      if(ZZType!=null&&!ZZType.equals("")&&!ZZType.equals("null"))
		      {
		    	  sql.append(" and dwzz0104 = '"+ZZType+"'");  
		      }
		      if(ZZLevel!=null&&!ZZLevel.equals("")&&!ZZLevel.equals("null"))
		      {
		    	  sql.append(" and dwzz0105 like '"+ZZLevel+"'");  
		      }
			try {
				ps = conn.prepareStatement(sql.toString());
				rs=ps.executeQuery();
				while(rs.next())
				{
					dwqk01=new Dwqk01();
					dwqk01.setDwqk0101(Integer.parseInt(rs.getString("dwqk0101")));
					dwqk01.setDwqk0102(rs.getString("dwqk0102"));
					dwqk01.setDwqk0103(rs.getString("dwqk0103"));
					dwqk01.setDwqk0104(rs.getString("dwqk0104")==null?"":rs.getString("dwqk0104"));
					dwqk01.setDwqk0105(rs.getString("dwqk0105"));
					dwqk01.setDwqk0106(rs.getString("dwqk0106"));
					dwqk01.setDwqk0107(rs.getString("dwqk0107"));
					dwqk01.setDwqk0108(rs.getString("dwqk0108"));
					dwqk01.setDwqk0109(rs.getDouble("dwqk0109"));
					dwqk01.setDwqk0110(rs.getString("dwqk0110"));
					dwqk01.setDwqk0111(rs.getString("dwqk0111"));
					dwqk01.setDwqk0113(rs.getString("dwqk0113"));
					dwqk01.setDwqk0114(rs.getString("dwqk0114"));
					dwqk01.setDwqk0116(rs.getString("dwqk0116"));
					dwqk01.setDwqk0117(rs.getString("dwqk0117"));
					dwqk01.setDwqk0118(rs.getString("dwqk0118"));
					dwqk01.setDwzz0104(rs.getString("dwzz0104"));
					dwqk01.setDwzz0105(rs.getString("dwzz0105"));
					dwqk01List.add(dwqk01);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return dwqk01List;
	}
	public List<Dwqk01> SearchDwqk(String DWQK0102,String DWQK0103, String DWQK0104s,String DWQK0104e,String DWQK0105,
			String DWQK0107, String DWQK0108,String DWQK0110,String DWQK0116,String DWQK0117) {
		Dwqk01 dwqk01=null;
		List<Dwqk01> dwqk01List=new ArrayList<Dwqk01>();
		   conn=JdbcUtil.getConnection();
		   StringBuffer sql = new StringBuffer();	
		   		sql.append("select * from dwqk01 where 1=1 ");
		   		if(DWQK0102!=null&&!DWQK0102.equals("")&&!DWQK0102.equals("null"))
		      {
		    	  sql.append(" and DWQK0102 like '%"+DWQK0102+"%'");  
		      }
		      if(DWQK0103!=null&&!DWQK0103.equals("")&&!DWQK0103.equals("null"))
		      {
		    	  sql.append(" and DWQK0103 like '%"+DWQK0103+"%'");  
		      }
		      if(DWQK0104s!=null&&!DWQK0104s.equals("")&&!DWQK0104s.equals("null"))
		      {
		    	  sql.append(" and DWQK0104>'"+DWQK0104s+"'");  
		      }
		      if(DWQK0104e!=null&&!DWQK0104e.equals("")&&!DWQK0104e.equals("null"))
		      {
		    	  sql.append(" and DWQK0104<='"+DWQK0104e+"'");  
		      }
		  	if(DWQK0105!=null&&!DWQK0105.equals("")&&!DWQK0105.equals("null"))
		      {
		    	  sql.append(" and DWQK0105 like '%"+DWQK0105+"%'");  
		      }
		      if(DWQK0107!=null&&!DWQK0107.equals("")&&!DWQK0107.equals("null"))
		      {
		    	  sql.append(" and DWQK0107='"+DWQK0107+"'");  
		      }
		      if(DWQK0110!=null&&!DWQK0110.equals("")&&!DWQK0110.equals("null"))
		      {
		    	  sql.append(" and DWQK0110 like '%"+DWQK0110+"%'");  
		      }
		      if(DWQK0116!=null&&!DWQK0116.equals("")&&!DWQK0116.equals("null"))
		      {
		    	  sql.append(" and DWQK0116 like '%"+DWQK0116+"%'");  
		      }
		      if(DWQK0117!=null&&!DWQK0117.equals("")&&!DWQK0117.equals("null"))
		      {
		    	  sql.append(" and DWQK0117 like '%"+DWQK0117+"%'");  
		      }
			try {
				ps = conn.prepareStatement(sql.toString());
				rs=ps.executeQuery();
				while(rs.next())
				{
					dwqk01=new Dwqk01();
					dwqk01.setDwqk0101(Integer.parseInt(rs.getString("dwqk0101")));
					dwqk01.setDwqk0102(rs.getString("dwqk0102"));
					dwqk01.setDwqk0103(rs.getString("dwqk0103"));
					dwqk01.setDwqk0104(rs.getString("dwqk0104")==null?"":rs.getString("dwqk0104"));
					dwqk01.setDwqk0105(rs.getString("dwqk0105"));
					dwqk01.setDwqk0106(rs.getString("dwqk0106"));
					dwqk01.setDwqk0107(rs.getString("dwqk0107"));
					dwqk01.setDwqk0108(rs.getString("dwqk0108"));
					dwqk01.setDwqk0109(rs.getDouble("dwqk0109"));
					dwqk01.setDwqk0110(rs.getString("dwqk0110"));
					dwqk01.setDwqk0111(rs.getString("dwqk0111"));
					dwqk01.setDwqk0113(rs.getString("dwqk0113"));
					dwqk01.setDwqk0114(rs.getString("dwqk0114"));
					dwqk01.setDwqk0116(rs.getString("dwqk0116"));
					dwqk01.setDwqk0117(rs.getString("dwqk0117"));
					dwqk01.setDwqk0118(rs.getString("dwqk0118"));
					dwqk01List.add(dwqk01);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return dwqk01List;
	}
	public Boolean UpdatDwqk(Dwqk01 dwqk01) {
		  Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "update dwqk01 set  dwqk0102=?,dwqk0103 =?,dwqk0104=?,dwqk0105=?,dwqk0106=?,dwqk0107=?,dwqk0108=?,dwqk0109=?,dwqk0110=?,dwqk0111=?,dwqk0113=?,dwqk0114=?,dwqk0116=?,dwqk0117=? where DWQK0101=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, dwqk01.getDwqk0102());
				ps.setString(2, dwqk01.getDwqk0103());
				ps.setString(3, dwqk01.getDwqk0104());
				ps.setString(4, dwqk01.getDwqk0105());
				ps.setString(5, dwqk01.getDwqk0106());
				ps.setString(6, dwqk01.getDwqk0107());
				ps.setString(7, dwqk01.getDwqk0108());
				ps.setDouble(8, dwqk01.getDwqk0109());
				ps.setString(9, dwqk01.getDwqk0110());
				ps.setString(10, dwqk01.getDwqk0111());
				ps.setString(11, dwqk01.getDwqk0113());
				ps.setString(12, dwqk01.getDwqk0114());
				ps.setString(13, dwqk01.getDwqk0116());
				ps.setString(14, dwqk01.getDwqk0117());
				ps.setInt(15, dwqk01.getDwqk0101());
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	public Boolean DelDwqk(String DWQK0101) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "delete from dwqk01  where DWQK0101=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, DWQK0101);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	public List<Dwqk01> SearchAllDwqk() {
		Dwqk01 dwqk01=null;
		List<Dwqk01> dwqk01List=new ArrayList<Dwqk01>();
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "select * from dwqk01";
			try {
				ps = conn.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					dwqk01=new Dwqk01();
					dwqk01.setDwqk0101(rs.getInt("dwqk0101"));
					dwqk01.setDwqk0102(rs.getString("dwqk0102"));
					dwqk01.setDwqk0103(rs.getString("dwqk0103"));
					dwqk01.setDwqk0104(rs.getString("dwqk0104")==null?"":rs.getString("dwqk0104"));
					dwqk01.setDwqk0105(rs.getString("dwqk0105"));
					dwqk01.setDwqk0106(rs.getString("dwqk0106"));
					dwqk01.setDwqk0107(rs.getString("dwqk0107"));
					dwqk01.setDwqk0108(rs.getString("dwqk0108"));
					dwqk01.setDwqk0109(rs.getDouble("dwqk0109"));
					dwqk01.setDwqk0110(rs.getString("dwqk0110"));
					dwqk01.setDwqk0111(rs.getString("dwqk0111"));
					dwqk01.setDwqk0113(rs.getString("dwqk0113"));
					dwqk01.setDwqk0114(rs.getString("dwqk0114"));
					dwqk01.setDwqk0116(rs.getString("dwqk0116"));
					dwqk01.setDwqk0117(rs.getString("dwqk0117"));
					dwqk01.setDwqk0118(rs.getString("dwqk0118"));
					dwqk01List.add(dwqk01);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return dwqk01List;
	}

}
